How to set server option using T-Sql ("user options")

[Gary Andrews]

One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..

Using T-SQL, how are these set?

sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go

i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?

Sorry, but I don't feel BOL is clear on how to set these options.

TIA Gary

[Pan]

Gary,

You're right that BOL is not clear about it. Your assumption is also right. The question arises why are doing this using T-sql and not EM?



------------
Gary Andrews at 7/31/2002 2:09:59 PM

One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..

Using T-SQL, how are these set?

sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go

i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?

Sorry, but I don't feel BOL is clear on how to set these options.

TIA Gary

[sonaliak]

I am creating indexed view for sql server 2000. I need to set these options to on for index view to work. I tried this

Alter Database v52devsq SET arithabort On
Alter Database v52devsq SET ANSI_NULLS On
Alter Database v52devsq SET CONCAT_NULL_YIELDS_NULL On
Alter Database v52devsq SET QUOTED_IDENTIFIER On

But I get errors all over in the application
SQL command executed: EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. Error Code SQL or AFX: -1
Then I read that one needs to set these at the server level. But after reading BOL or MSDN I still cannot figure out how to do this. I need to use Transact SQL as I will need to put this as a script in the application build.
Any help
Thanks

[rmiao]

Just run set commands in query analyzer, check sql books online for syntax. I think you have to set them when create db.

[sonaliak]

BOL is no help.. they do not have this specified.
I need to set these 4 settings on the server level for index view to work and need it in transact sql.. its not in BOL.
I figured out some part of it...
arithabort is 64
quoted identifier is 256
ansi nulls 32
concat null yields nills 4096
and ansi warnings is 8

this adds up to 4456
So I did this connecting as MASTER
sp_configure 'user options' , 4456
go
RECONFIGURE
go

I got rid of all other errors with arithabort and all EXCEPT

the one with quoted identifier
EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
go

Server: Msg 1934, Level 16, State 1, Procedure pr_et_delExpense, Line 40
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

How do I fix this -
I did as above server level setting
I tried doing
Alter Database v52devsq SET QUOTED_IDENTIFIER On

this one
USE master
EXEC sp_dboption 'v52devsq', 'quoted identifier', 'TRUE'

also this one
set QUOTED_IDENTIFIER on
go

EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
go

Non of this is working I keep getting error with
quoted identifier. How do I fix it ?

I created a test indexed view like this -

/*Expenses both project Currency and Base Currency*/
drop index iv_expenses.iv_expenses_idx
go
DROP VIEW iv_expenses
GO
CREATE VIEW iv_expenses WITH SCHEMABINDING
AS
select work_id,Period_ID, sum(EDtl_ProjAmt) as expproj, sum(EDtl_BaseAmt) as expbase, count_big(*) as count
from dbo.ETExpDTL , dbo.mwebwork, dbo.ETGLCMPNY, dbo.ETActivity, dbo.mwebFiscalPeriod
where Work_ID= Act_Impt_ID and Act_ID=EDtl_Activity
and work_entity_type in (3, 4) and Work_GLCompany = GLCo_Company
and EDtl_TranDate between Period_Start and Period_Finish
and Period_Level = 3
GROUP BY work_id, Period_ID
GO

CREATE UNIQUE CLUSTERED INDEX iv_expenses_idx ON iv_expenses (Work_ID, period_id)
GO

Any help ?

[rmiao]

You can set those options in enterprise manager as well.